package org.magicsoft.office.excel.impt;

import java.sql.Connection;

import org.magicsoft.office.db.DatabaseType;
import org.magicsoft.office.db.DatabaseUtils;
import org.magicsoft.office.excel.model.Column;
import org.magicsoft.office.excel.model.Sheet;
import org.magicsoft.office.excel.model.TargetTable;
import org.magicsoft.office.exception.ModelException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class PrimaryKeyUpdater {

	private static final Logger logger = LoggerFactory.getLogger(PrimaryKeyUpdater.class);

	public void update(Sheet sheet, Connection conn, DatabaseType databaseType) {
		int i = 0;
		Column column;
		StringBuilder sqlBuilder;
		String sql;
		for (TargetTable targetTable : sheet.getTargetTables()) {
			if (targetTable.getUpdateByColumns() == null) {
				continue;
			}

			sqlBuilder = new StringBuilder();
			sqlBuilder.append("update ");
			sqlBuilder.append(sheet.getTempTableName());
			sqlBuilder.append(" set (");
			sqlBuilder.append(targetTable.getOidColumn());
			sqlBuilder.append(") = (select ");
			sqlBuilder.append(targetTable.getPrimaryKey().getColumn());
			sqlBuilder.append(" from ");
			sqlBuilder.append(targetTable.getTableName());
			sqlBuilder.append(" where ");
			for (String updateByColumn : targetTable.getUpdateByColumns()) {
				if (i++ != 0) {
					sqlBuilder.append(" and ");
				}

				column = sheet.getColumn(updateByColumn);
				if (column.getBindColumn() == null) {
					throw new ModelException("列：“" + column.getColumnNumber() + "”没有绑定数据库字段。");
				}
				sqlBuilder.append(column.getBindTable().getTableName());
				sqlBuilder.append(".");
				sqlBuilder.append(column.getBindColumn());
				sqlBuilder.append(" = ");
				sqlBuilder.append(sheet.getTempTableName());
				sqlBuilder.append(".");
				sqlBuilder.append(column.getTempTableColumn());
			}
			sqlBuilder.append(")");
			sql = DatabaseUtils.translateJoinUpdate(sqlBuilder.toString(), databaseType);
			logger.debug("update oid sql:" + sql);
			DatabaseUtils.executeUpdate(conn, sql);

			sqlBuilder = new StringBuilder();
			sqlBuilder.append("update ");
			sqlBuilder.append(sheet.getTempTableName());
			sqlBuilder.append(" set ");
			sqlBuilder.append(targetTable.getNidColumn());
			sqlBuilder.append(" = ");
			sqlBuilder.append(targetTable.getOidColumn());
			sqlBuilder.append(" where ");
			sqlBuilder.append(targetTable.getOidColumn());
			sqlBuilder.append(" is not null");
			sql = sqlBuilder.toString();
			logger.debug("update nid sql:" + sql);
			DatabaseUtils.executeUpdate(conn, sql);
		}
	}

}
